/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.BugRS.dao;

import com.aptech.BugRS.enity.CustomerDTO;
import com.aptech.BugRS.util.Configure;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author KISS
 */
public class CustomerDAO {

    private Configure db = null;
    private Connection con = null;
    private ResultSet rs = null;
    private PreparedStatement pst = null;
    private String lastError = "";

    /**
     * Create a new instance of D_Customer
     */
    public CustomerDAO() {
        db = new Configure();
    }

    /**
     * @return list of Customer
     */
    public ArrayList<CustomerDTO> ReadByAll() {

        ArrayList<CustomerDTO> list = new ArrayList<CustomerDTO>();
        con = db.getConnection();
        String sql = "SELECT * FROM Customer";
        try {
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                CustomerDTO cus = new CustomerDTO();
                cus.setCustomerID(rs.getInt(1));
                cus.setFirstName(rs.getString(2));
                cus.setLastName(rs.getString(3));
                cus.setAddress(rs.getString(4));
                cus.setEmail(rs.getString(5));
                cus.setPhone(rs.getString(6));
                cus.setUserName(rs.getString(7));
                cus.setPassWord(rs.getString(8));
                list.add(cus);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error");
        } finally {
            db.closeConnection();
        }
        return list;
    }

    /**
     * Return Customer by ID
     * @param id
     * @return
     */
    public CustomerDTO ReadByID(int id) {
        CustomerDTO cus = new CustomerDTO();
        try {
            con = db.getConnection();
            String sql = "SELECT * FROM Customer WHERE CustomerID = ?";
            pst = con.prepareStatement(sql);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            cus.setCustomerID(rs.getInt(1));
            cus.setFirstName(rs.getString(2));
            cus.setLastName(rs.getString(3));
            cus.setAddress(rs.getString(4));
            cus.setEmail(rs.getString(5));
            cus.setPhone(rs.getString(6));
            cus.setUserName(rs.getString(7));
            cus.setPassWord(rs.getString(8));
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        }
        return cus;
    }

    /**
     * Read by Email of Customer
     * @param email
     * @return Customer
     */
    public CustomerDTO ReadByEmail(String email) {
        CustomerDTO cus = new CustomerDTO();
        try {
            con = db.getConnection();
            String sql = "SELECT * FROM Customer WHERE CustomerID = ?";
            pst = con.prepareStatement(sql);
            pst.setString(1, email);
            rs = pst.executeQuery();
            cus.setCustomerID(rs.getInt(1));
            cus.setFirstName(rs.getString(2));
            cus.setLastName(rs.getString(3));
            cus.setAddress(rs.getString(4));
            cus.setEmail(rs.getString(5));
            cus.setPhone(rs.getString(6));
            cus.setUserName(rs.getString(7));
            cus.setPassWord(rs.getString(8));
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        }
        return cus;
    }

    /**
     * Read list of Customer by LastName
     * @param lastname
     * @return List of Customer
     */
    public ArrayList<CustomerDTO> ReadByName(String lastname) {
        ArrayList<CustomerDTO> list = new ArrayList<CustomerDTO>();
        try {
            String sql = "SELECT * FROM Customer WHERE LastName = ?";
            pst = con.prepareStatement(sql);
            pst.setString(1, lastname);
            rs = pst.executeQuery();
            while (rs.next()) {
                CustomerDTO cus = new CustomerDTO();
                cus.setCustomerID(rs.getInt(1));
                cus.setFirstName(rs.getString(2));
                cus.setLastName(rs.getString(3));
                cus.setAddress(rs.getString(4));
                cus.setEmail(rs.getString(5));
                cus.setPhone(rs.getString(6));
                cus.setUserName(rs.getString(7));
                cus.setPassWord(rs.getString(8));
                list.add(cus);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CustomerDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    /**
     * Method add new customer
     * @param cus
     * @return boolean
     */
    public boolean Create(CustomerDTO cus) {

        ArrayList<CustomerDTO> list = ReadByAll();
        /*
         * Check exits in database
         */
        for (CustomerDTO cu : list) {
            if (cu.getCustomerID() == cu.getCustomerID()) {
                setLastError("Complaint is exitsed! Add fail!");
                return false;
            }
        }

        /*
         * Check validate
         */
        //        if (!cus.validate()) {
        //            setLastError(cus.getLastError());
        //            return false;
        //        }
        try {
            con = db.getConnection();
            String sql = "INSERT INTO Customer VALUES(?,?,?,?,?,?,?)";
            pst = con.prepareStatement(sql);
            pst.setInt(1, cus.getCustomerID());
            pst.setString(2, cus.getFirstName());
            pst.setString(3, cus.getLastName());
            pst.setString(4, cus.getAddress());
            pst.setString(5, cus.getEmail());
            pst.setString(6, cus.getPhone());
            pst.setString(7, cus.getUserName());
            pst.setString(8, cus.getPassWord());
            if (pst.executeUpdate() > 0) {
                setLastError("Add Customer Successfully!");
                db.closeConnection();
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
            db.closeConnection();
            return false;
        }
        setLastError("Add fail!");
        db.closeConnection();
        return false;
    }

    /**
     * Method Update a Customer
     * @param cus
     * @return boolean
     */
    public boolean Update(CustomerDTO cus) {
        try {
            con = db.getConnection();
            String sql = "SELECT * FROM Customer WHERE CustomerID = ?";
            pst = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, cus.getCustomerID());
            rs = pst.executeQuery();
            rs.first();
            rs.updateString(2, cus.getFirstName());
            rs.updateString(3, cus.getLastName());
            rs.updateString(4, cus.getAddress());
            rs.updateString(5, cus.getEmail());
            rs.updateString(6, cus.getPhone());
            rs.updateString(7, cus.getUserName());
            rs.updateString(8, cus.getPassWord());
            rs.updateRow();
            db.closeConnection();
            setLastError("Update Customer successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError("SQL Error");
            db.closeConnection();
            return false;
        }
    }

    /**
     *
     * @param customer
     * @return boolean
     */
    public boolean isExits(CustomerDTO cus) {
        try {
            String sql = "SELECT * FROM Customer WHERE CustomerID = ?";
            con = db.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, cus.getCustomerID());
            rs = pst.executeQuery();
            /**
             * Exits return true
             * Not Exit return false;
             */
            if (rs.next()) {
                return true;
            }

        } catch (SQLException ex) {
            setLastError("SQL Error");
        }
        return false;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
